package com.weifly.jettycn.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.weifly.jettycn.TableConst;
import com.weifly.jettycn.domain.Project;
import com.weifly.jettycn.util.QueryInfo;

public class ProjectDao extends JdbcDaoSupport {

	/**
	 * 获得项目列表
	 * 
	 * @param queryInfo
	 * @param catalogList
	 * @return
	 */
	public List<Project> queryProjectList(QueryInfo queryInfo, List<String> catalogList){
		StringBuilder sb = new StringBuilder();
		sb.append("select * from ").append(TableConst.TABLE_PROJECT);
		
		if(catalogList!=null && catalogList.size()>0){
			sb.append(" where c_catalogid in(");
			for(int i=0;i<catalogList.size();i++){
				String s = catalogList.get(i);
				if(i==0){
					sb.append("'").append(s).append("'");
				}else{
					sb.append(",'").append(s).append("'");
				}
			}
			sb.append(")");
		}
		
		sb.append(" order by t_update desc");
		
		if(queryInfo.getPage()>0){
			int start = (queryInfo.getPage()-1)*queryInfo.getPageCount();
			sb.append(" limit ").append(start).append(" , ").append(queryInfo.getPageCount());
		}
		
		String sql = sb.toString();
		
		final List<Project> proList = new ArrayList<Project>();
		this.getJdbcTemplate().query(sql, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				while(rs.next()){
					Project p = populateProject(rs);
					proList.add(p);
				}
				return null;
			}
			
		});
		return proList;
	}

	/**
	 * 获得项目总数
	 * 
	 * @param catalogList 类别集合
	 */
	public int getProjectCountor(List<String> catalogList){
		String sql = "select count(*) from " + TableConst.TABLE_PROJECT;
		if(catalogList!=null && catalogList.size()>0){
			StringBuilder sb = new StringBuilder(" where c_catalogid in(");
			for(String s : catalogList){
				sb.append("'").append(s).append("',");
			}
			sb.setLength(sb.length()-1);
			sb.append(")");
			sql = sql + sb.toString();
		}
		
		Integer totalCount = this.getJdbcTemplate().query(sql, new ResultSetExtractor<Integer>(){

			public Integer extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if(rs.next()){
					return rs.getInt(1);
				}
				return null;
			}
			
		});
		return totalCount.intValue();
	}
	
	/**
	 * 根据名称查找project
	 */
	public List<Project> findProjectByName(String name){
		final List<Project> list = new ArrayList<Project>();
		String sql = "select * from " + TableConst.TABLE_PROJECT + " where c_name=?";
		this.getJdbcTemplate().query(sql, new Object[]{name}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				while(rs.next()){
					Project p = populateProject(rs);
					list.add(p);
				}
				return null;
			}
			
		});
		return list;
	}
	
	private Project populateProject(ResultSet rs) throws SQLException {
		Project pro = new Project();
		
		pro.setPrjid(rs.getString("c_prjid"));
		pro.setName(rs.getString("c_name"));
		pro.setHomepage(rs.getString("c_homepage"));
		pro.setAuthor(rs.getString("c_author"));
		pro.setDescription(rs.getString("c_description"));
		pro.setCatalogid(rs.getString("c_catalogid"));
		pro.setUpdate(rs.getTimestamp("t_update"));
		
		return pro;
	}
	
	/**
	 * 插入project
	 */
	public void insertProject(final Project project){
		String sql = "insert into " + TableConst.TABLE_PROJECT + "(c_prjid, c_name, c_homepage, c_author, c_description, c_catalogid, t_update) values(?,?,?,?,?,?,?)";
		this.getJdbcTemplate().update(sql, new PreparedStatementSetter(){

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, project.getPrjid());
				ps.setString(2, project.getName());
				ps.setString(3, project.getHomepage());
				ps.setString(4, project.getAuthor());
				ps.setString(5, project.getDescription());
				ps.setString(6, project.getCatalogid());
				ps.setTimestamp(7, project.getUpdate()==null ? null : new java.sql.Timestamp(project.getUpdate().getTime()));
			}
			
		});
	}
	
	/**
	 * 更新project
	 */
	public void updateProject(final Project project){
		String sql = "update " + TableConst.TABLE_PROJECT + " set c_name=?, c_homepage=?, c_author=?, c_description=?, c_catalogid=? where c_prjid=?";
		this.getJdbcTemplate().update(sql, new PreparedStatementSetter(){

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, project.getName());
				ps.setString(2, project.getHomepage());
				ps.setString(3, project.getAuthor());
				ps.setString(4, project.getDescription());
				ps.setString(5, project.getCatalogid());
				ps.setString(6, project.getPrjid());
			}
			
		});
	}
	
	/**
	 * 根据id获得project
	 */
	public Project getProject(String projectid){
		String sql = "select * from " + TableConst.TABLE_PROJECT + " where c_prjid=?";
		return this.getJdbcTemplate().query(sql, new Object[]{projectid}, new ResultSetExtractor<Project>(){

			public Project extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if(rs.next()){
					Project project = populateProject(rs);
					return project;
				}
				return null;
			}
			
		});
	}
	
	/**
	 * 根据id删除project
	 */
	public void deleteProject(String projectid){
		String sql = "delete from " + TableConst.TABLE_PROJECT + " where c_prjid=?";
		this.getJdbcTemplate().update(sql, new Object[]{projectid});
	}
}
